Click to return to The Delphi Magazine home page...

Paradox File Corruption

Based on an article in The Delphi Magazine, Issue 42, February 1999, and related extract from The Delphi Clinic, Issue 17, this article is Copyright © 1999 Brian Long. The article was written by Brian Long (brian@blong.com) with help from Will Watts, Nick Moon, David Rose, Nick Spurrier, Andy Race and Jack Birrell.

Download The Source Code
Full updated source code ready to plug into your projects: Paradox.zip 5184 bytes

• Paradox File Corruption, Issue 42
• The Delphi Clinic, Issue 17: Paradox Table Corruption

A reader of The Delphi Magazine wrote to me with the following query:

I read your summary of precautions regarding Paradox table and index corruption in multi-user applications on page 52 of Issue 17, implemented all the measures, but I still get corrupted tables and index files every now and again. Are there any more things you can think of?

Well, indeed there are. Since writing that entry I have been collecting other useful corruption avoiding tips and techniques that I will list here. First of all though, below is a summary of what was highlighted in Issue 17.

Another useful piece of advice is to ensure you set the Session object’s PrivateDir property to some suitable private directory. Perhaps a subdirectory of your application’s directory. You can get your application’s directory with ExtractFilePath(Application. ExeName). Avoid leaving PrivateDir blank as it will default to the current directory, which will more than likely be the application directory. Letting the BDE use your application directory as its temporary working directory can lead to Lock file has grown too big exceptions. Also, avoid pointing PrivateDir at a root directory, make sure it points at a subdirectory.

One final point on the private directory subject is that the private directory should have access to a reasonable amount of disk space. A query linked across tables to extract data may take up at least three times the size of largest table for its temporary __*.* files.

If your application is terminated unexpectedly, your BDE private directory might have its temporary files left in place, instead of being deleted as they would normally be by the BDE. If you think this might be a problem, you could write a section of code that executes at the beginning of your application, before any tables are opened, that deletes files from the private directory conforming to the specification __qb*.*.

Since the Local Share setting is so crucial, one idea is to only let the program run if Local Share has a value of True. Your main form’s OnCreate handler could call a routine like this.

procedure CheckLocalShare;
var ASYSConfig: SYSConfig;
begin
 {$ifdef Win32}
  { Ensure BDE is initialised }
  Session.Open;
 {$endif}
  if (DbiGetSysConfig(ASYSConfig) = DbiErr_None) and
    not ASYSConfig.bLocalShare then begin
    ShowMessage('BDE''s LOCAL SHARE flag must be TRUE for this ' +
      'program to run. Ask your System Administrator to do this for ' +
      'you.'#13#13'This program will not continue until this change ' +
      'has been made and all BDE applications have been restarted');
   {$ifdef Win32}
    Application.ShowMainForm := False;
   {$endif}
    Application.Terminate;
  end
end;

In Delphi 2 or later you can also read Local Share in a more VCL-esque fashion by making use of

Session.GetConfigParams('\System\Init', SomeTStringsObject)

SomeTStringsObject.Values['LOCAL SHARE']

For all Windows 95 machines running BDE applications accessing Paradox or dBASE data or containing that data you should take the following steps. Firstly, make sure you have at least version 4.00.1116 of VREDIR.VXD (156,773 bytes, 11^th Sep 97, 11:16) and version 4.00.1112 of VNETSUP.VXD (17,595 bytes, 30th May 97, 11:12). These can be installed with the patch program vrdrupd.exe located at http://support.microsoft.com/Download/support/mslfiles/vrdrupd.exe and fix a problem in Windows 95 where an application shares data with a Windows NT server, as described in Microsoft’s Knowledge Base articles Q148367 and Q174371. Note that Microsoft’s articles get the date stamp of VREDIR.VXD wrong and suggest it is 2 Jun 97.

Next, VREDIR must also be set up correctly: ensure that the binary registry value

HKey_Local_Machine\System\CurrentControlSet\Services\VxD\VREDIR\DiscardCacheOnOpen

is set to 01. The machine will need rebooting for this setting to take effect.

Plus, Windows 95 caching should be disabled. To do this, launch the System Properties dialog by holding down the Alt key and double clicking on My Computer (or by holding down the Windows key and pressing Pause). Click the Performance tab, press the File System... button and click on the Troubleshooting tab. Check the options: Disable write-behind caching for all drives. This corresponds to setting this binary value to 0:

HKey_Local_Machine\System\CurrentControlSet\Control\FileSystem\DriveWriteBehind

In addition to the above setting there is some suggestion that the Disable synchronous buffer commits and Disable new file sharing and locking semantics options should also be checked, although the help for these options does not really uphold this. This equates to changing the binary settings AsyncFileCommit to 1 and SoftCompatMode to 0.

If you are running Windows for Workgroups 3.1x, you should disable disk write caching. In SYSTEM.INI in the [386Enh] section and add an entry to disable 32-bit write-caching on all drives that have shared data on them, eg ForceLazyOff=CDE. Microsoft KnowledgeBase article Q107645 describes this option and article Q108109 emphasises that [386Enh] is the correct section, not [vcache] as mentioned in the Resource Kit documentation. If AUTOEXEC.BAT loads the SMARTDRV.EXE program, make sure the /X parameter is used to disable write-behind caching.

The sample project DBStuff.Dpr (which can be found in the PARADOX.ZIP file) contains some code which will check all the appropriate entries depending on whether you are running your application on Windows 95, NT or 3.1. In fact, so potentially important are the registry settings that if they are not found to have the correct values, the program sets them and suggests that the user reboots. DBStuff.Dpr compiles in all versions of Delphi from 1 to 4.

The reboot dialog comes from RestartDialog, an undocumented Shell32 API described in the Restarting Windows entry in Issue 40’s Delphi Clinic. Since I am not passing any text to the API I don’t need to worry about the Unicode issue highlighted in that write-up.

The code below shows the form’s OnCreate handler, the main registry checking routine and the Windows 95 checking code. You can see that several helper routines are not listed, including the code that checks the version information of the network redirector files. Refer to the project on the disk for all the missing code. One helper routine listed in full is CheckRegistryEntry. This verifies both numeric and string registry values (passed as a Variant parameter), setting them if they are either wrong or missing. If a change was made, the Reboot Required flag is set to True.

procedure TForm1.FormCreate(Sender: TObject);
begin
  CheckOKForParadoxAppToRun
end;
...
procedure CheckRegistryIsAcceptable;
begin
 {$ifdef Win32}
  case Win32Platform of
    VER_PLATFORM_WIN32_WINDOWS : CheckWin95Registry;
    VER_PLATFORM_WIN32_NT      : CheckWinNTRegistry;
  end;
  if RebootRequired then
    //Use standard Win32 reboot dialog
    RestartDialog(0, nil, ew_RestartWindows)
 {$else}
  CheckWin31Registry;
  if RebootRequired then begin
    ShowMessage('Some system settings have been changed '+
      '- Windows needs to restart');
    ExitWindows(ew_RestartWindows, 0)
  end
 {$endif}
end;
...
procedure CheckRegistryEntry(Reg: TRegistry;
  const Path, Value: String;
  const Default, Desired: Variant; Size: Byte);
var
  TmpInt: Cardinal;
  TmpStr: String;
begin
  with Reg do
    if OpenKey(Path, True) then
      try
        case VarType(Desired) of
          varInteger:
            { Some numbers need to be stored as DWORD values, }
            { while some need to be stored as binary values }
            if Size = 0 then
            begin
              if not ValueExists(Value) or
                 (ReadInteger(Value) = Default) then
              begin
                WriteInteger(Value, Desired);
                RebootRequired := True
              end
            end
            else
            begin
              TmpInt := Default;
              if ValueExists(Value) then
                ReadBinaryData(Value, TmpInt, Size);
              if TmpInt = Default then
              begin
                TmpInt := Desired;
                WriteBinaryData(Value, TmpInt, Size);
                RebootRequired := True
              end
            end;
          varString:
            begin
              if not ValueExists(Value) or
                 (ReadString(Value) = Default) then
              begin
                WriteString(Value, Desired);
                RebootRequired := True
              end
            end
        end
      finally
        CloseKey
      end
end;

const
  Control  = 'System\CurrentControlSet\Control\';
  Services = 'System\CurrentControlSet\Services\';

procedure CheckWin95Registry;
var
  Reg: TRegistry;
const
  DOSRequester =
    'Network\Novell\System Config\Netware Dos Requester';
begin
  Reg := TRegistry.Create;
  try
    Reg.RootKey := HKey_Local_Machine;
    //Fix VREDIR.VxD settings
    CheckRegistryEntry(Reg, Services + 'VxD\VREDIR',
      'DiscardCacheOnOpen', 0, 1, SizeOf(Byte));
    //Fix NWREDIR.VxD settings
    CheckRegistryEntry(Reg, Services + 'VxD\NWREDIR',
      'ReadCaching', 1, 0, SizeOf(Byte));
    //Fix Novell settings
    CheckRegistryEntry(Reg, DOSRequester, 'Cache Writes',
      'Yes', 'No', 0);
    CheckRegistryEntry(Reg, DOSRequester, 'Opportunistic
      Locking', 'Yes', 'No', 0);
    //Fix FileSystem troubleshooting settings
    CheckRegistryEntry(Reg, Control + 'FileSystem',
      'DriveWriteBehind',
      $FFFFFFFF, 0, SizeOf(Longint));
   {$define AllOptionsThatPeopleSuggest}
   {$ifdef AllOptionsThatPeopleSuggest}
    CheckRegistryEntry(Reg, Control + 'FileSystem',
      'SoftCompatMode', 1, 0, SizeOf(Longint));
    CheckRegistryEntry(Reg, Control + 'FileSystem',
     'AsyncFileCommit', 0, 1, SizeOf(Byte));
   {$endif}
  finally
    Reg.Free
  end
end;
...
procedure CheckOKForParadoxAppToRun;
begin
 {$ifdef Win32}
  //Only Win95 redirector files need checking
  if Win32Platform = VER_PLATFORM_WIN32_WINDOWS then
    CheckRedirector;
 {$endif}
  CheckRegistryIsAcceptable;
  CheckLocalShare;
end;

It is important to remember that these operating system settings must be set on all machines either running BDE applications or containing BDE data. Similarly the BDE must have Local Share set to True on all installations. If a machine gets upgraded, or has the operating system re-installed, or has a new version of the BDE set up, some of these settings may need to be fixed again. The same applies if a new machine is added to the network.

One important point on this whole subject is that it helps enormously if you educate and train your users. It is not unheard of for users to pull the power cable from their PCs to plug into a kettle, or to terminate applications with the Ctrl+Alt+Del three-fingered salute.

See References below for other references which discuss some of these issues, sometimes in the context of totally non-Inprise, non-Paradox systems.

One final point to make is that if your Paradox tables do get corrupted, you can make use of TUtility.DLL to try and repair them. A BDE 5 compatible version of this DLL can be found at www.inprise.com/devsupport/bde/utilities.html.

Back to Top

Inprise Technical Information sheet TI3342 which offers more views on the Paradox corruption issue; note the file name is case sensitive: www.inprise.com/devsupport/bde/ti_list/TI3342.html

Q148367 discusses the Win95 redirector bug; to read any of the other MS KnowledgeBase articles you can build up a URL using the same pattern as this one: support.microsoft.com/support/kb/articles/q148/3/67.asp

Discussion of the Windows 95 redirector problem and NT opportunistic locking: www.turbopower.com/newsletters/0798/default.asp

Discussion of Novell settings to avoid data loss: www.daccess.com/Support/Integrating_DF_with_NetWare.htm

cc:Mail technical paper 144293 discussing various Windows registry settings that need to be set correctly to avoid data loss: orionweb.lotus.com/sims/c8f6_546.htm

cc:Mail technical paper 139714 discussing preferred NetWare settings to avoid data loss; if you use NetWare you should read this as it describes many preferred NetWare configuration settings not discussed here: orionweb.lotus.com/sims/8a76_546.htm

Article 2917002 describes a Novell read caching problem; article 2911461 describes potential data loss issues using Windows 95 against a NetWare 3.11 server: support.novell.com/search/kb_index.htm

Back to Top

Question:
I am developing a Paradox table application in Delphi 1 to run on Windows For Workgroups (WFWG) 3.11. During testing we have had various occurrences of records being lost, indexes out of date and indexes corrupted. Why might this be caused, and what can I do to avoid it? The network server PC is also used as a client PC running the .EXE and is known as network drive L to all other PCs. We've added a SUBST L: C:\ to that PC's AUTOEXEC.BAT, so it's IDAPI.CFG can also refer to L:\... for its net directory and database path. We're running WFWG in enhanced mode and so VSHARE.386 is running, so do I really need SHARE? We've tried running the .EXE on Windows 95 and besides the above problem it seems to run OK. Are there any other things we should consider/change before running it live on Windows 95?

In some cases, bad programming is the cause of the problem. It is important to ensure that records are posted. When you terminate a program, it is the responsibility of the developer to Post all un-posted records before the program is terminated, otherwise you will get a `record loss' problem. This could be achieved by applying something like the following statement for all your table objects in each form's OnClose event handler:

if TableObject.State in dsEditModes then
  TableObject.Post;

The program development phase is the time when most tables start inheriting corruption (caused by the developer resetting programs from time to time) which may not become evident until the system is deployed. One possible way of overcoming the problem is to rebuild the indexes periodically. This can be done with a table restructure (using the BDE DbiDoRestructure function, or with the Database Desktop) and will often resolve index corruption. It can also be done using TUtility, which can resolve data corruption (TUtility comes with the full version of Paradox and was included on the disk with Issue 5 of The Delphi Magazine, or look on the CompuServe BDEVTOOLS forum).

An alternative, and perhaps more foolproof, way of fixing broken indexes would be to write a routine which physically erases the indexes (with DeleteFile) and recreates the indexes from scratch with the BDE DbiRegenIndexes call (which relies on the table being opened exclusively). If your tables use referential integrity, then deleting the indexes may cause a problem due to a special checksum in the table header. In these cases you will need to delete the indexes and the .VAL file and use DbiDoRestructure to regenerate all tables that are involved in the referential integrity relationship.

In the BDE Configuration application on all PCs that will run the program, set Local Share to True on the System page. This ensures that lock files are written to the local hard disk, thereby ensuring that applications on other machines will be able to find the lock files. This should only be necessary for the machine where the data resides, however the general opinion is that it should always be turned on, provided you have file sharing functionality loaded with either SHARE or VSHARE. On peer to peer networks, the default setting of Local Share is a common cause of data loss.

When False, Local Share instructs the BDE to assume that all access to tables on `local drives' (a peer to peer LAN counts as `local') will occur via the same instance of the BDE in memory, it therefore fails in a number of situations including:

SUBST will disable 32-bit file access and may therefore slow the machine down. If 32-bit file access is disabled, VSHARE won't be loaded. Some would say that SUBST isn't very safe especially when used with Windows 95 and is probably provided by Microsoft for compatibility with old DOS applications. Apparently, there are little corners where Paradox does not function correctly and some people don't trust the command to work for all flavours of Wintel operating systems.

Recent revisions of the BDE allow different Sessions/Users to reference the PDOXUSRS.NET file using different drive letters so long as the remainder of the path is identical. Since the server has shared its root directory there is therefore no need to use SUBST and put up with the associated drawbacks: simply set the server machine's IDAPI.CFG Net Dir setting (on the Drivers page in the PARADOX driver settings) to C:\MYDIR and the workstations' Net Dir to L:\MYDIR. You can do this in code by assigning a value to Session.NetFileDir if you want to avoid editing IDAPI.CFG.

Note that using the root directory for the NET file can confuse certain BDE revisions. It is advisable to avoid this as a matter of policy even though the current revision seems happy with it. Also, if the user of the server does not want to share the entire C drive, it might be better to create a small partition for the Net Dir location.

If Local Share is True then the BDE will detect an incorrect NetFileDir and refuse to access the tables. If an incorrect assignment here is causing corruption then Local Share is still the real culprit.

On the Aliases page of the BDE Configuration application on all PCs in that will run the program, ensure the alias's Path points to the same network data directory.

Ensure that all users have their own private directory, preferably local. This is set with the Session object's PrivateDir property. Note that the online help specifies that if there will be multiple instances of the program running simultaneously on any one machine you should make sure each instance is given a unique path to avoid interference between temporary files from the different instances.

Call DbiSaveChanges after each table post (done simplest by putting the call in the table's AfterPost event handler). This should be unnecessary if the local share option has been set properly. When the BDE knows that the Paradox table is on a network, each record is saved to disk automatically. Therefore, DbiSaveChanges may only be necessary for saving local tables. There are two cases where a call to DbiSaveChanges can be a definite life saver: when you empty a table and when you restructure/pack a table (using DbiDoRestructure); this is because the actual table file is deleted and recreated but isn't necessarily committed to disk.

Instead of repeated calls to DbiSaveChanges, call DbiUseIdleTime in the Application's OnIdle event handler (also set the event handler's Done parameter to True). A call to DbiUseIdletime writes one dirty buffer to disk. Putting it in the OnIdle event means buffers will be written whenever your program is waiting for user input. Avoid using both DbiSaveChanges and DbiUseIdletime as they both do the same thing and so you'll be causing excessive function calls. This routine is becoming very popular as a general alternative to DbiSaveChanges, as it requires much less coding to use.

Have SHARE loaded with parameters of /F:4096 /L:40 as recommended by Borland. This advice is generally for Windows 3.10 users only. VSHARE from Windows for Workgroups supersedes SHARE: it's much better, although there is a caveat. Apparently VSHARE is a 32-bit driver which won't work with 16-bit disk controllers/drives which are present on non-local bus IDE PCs. In those circumstances, excluding SHARE from AUTOEXEC.BAT, and enabling VSHARE from Control Panel causes an error from IDAPI indicating SHARE isn't loaded. Write a message handler for wm_EndSession in your main form class. Delphi 1 doesn't automatically handle this message which is sent when Windows is shut down by the user (although Delphi 2 does). Consequently, if a Delphi app is running when Windows is terminated, it won't be closed properly, and so BDE buffers may remain unwritten. It would be good practice to call Halt on receipt of a wm_EndSession message handler. Halt is not normally an advisable way to close a program, usually we use Application.Terminate. However that operates by posting (as opposed to sending) a wm_Quit message and so won't get around to doing what it needs to before Windows is gone. Halt causes exit procedures to be called, including the one in the DB unit which frees the Session object, thereby closing down the BDE in a proper fashion.

The code below shows part of a form unit which takes up some of these suggestions. This code is from the project LOSS.DPR which can be found in the PARADOX.ZIP file.

  TForm1 = class(TForm)
  ...
  public
    procedure DoIdle(Sender: TObject; var Done: Boolean);
   {$ifdef VER80}
    procedure WMEndSession(var Msg: TWMEndSession);
      message wm_EndSession;
   {$endif}
  end;
...
uses DbiProcs;
...
procedure TForm1.FormCreate(Sender: TObject);
begin
  Application.OnIdle := DoIdle;
end;
procedure TForm1.DoIdle(Sender: TObject; var Done: Boolean);
begin
  { Each idle period, write a dirty buffer to disk }
  DbiUseIdleTime;
  Done := True;
end;
procedure TForm1.FormClose(Sender: TObject; var Action: TCloseAction);
var Loop: Integer;
begin
  {Generic way of ensuring all table changes are saved when form is closed}
  for Loop := 0 to ComponentCount - 1 do
   if Components[Loop] is TDataSet then
     with TDataSet(Components[Loop]) do
       if State in dsEditModes then
         Post;
end;
{$ifdef VER80}
procedure TForm1.WMEndSession(var Msg: TWMEndSession);
begin
  { If session is ending, call Halt to get exit routines executed. The DB
    unit's exit routine frees the Session object, which will unload the
    BDE, flushing any unsaved changes to disk }
  if Msg.EndSession then
    Halt;
end;
{$endif}

Back to Top